﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Configuration;
using Oracle.DataAccess.Client;
using Wicresoft.UnifyShow2.Portal.Models;
using Wicresoft.UnifyShow.Business;
using Microsoft.Practices.Unity;
using Wicresoft.UnifyShow.BusinessImpl;
using System.Text;
using System.Linq.Expressions;

namespace Wicresoft.UnifyShow2.Portal.Controllers
{
    [Authorize]
    public class ITSMController : BaseController
    {
        private IUser_X user_x;
        private static decimal usersiteid;
        private static string usersite;

        protected override void Dispose(bool disposing)
        {
            ((IDisposable)user_x).Dispose();
        }

        [InjectionConstructor]
        public ITSMController(IUser_X _user_x)
        {
            user_x = _user_x;
        }
        //
        // GET: /ITSM/

        public ActionResult Index()
        {
            SetRoles(user_x);
            ViewBag.IsConfigAdmin = IsConfigAdmin;
            var USER_X = user_x.GetUser_XByName(User.Identity.Name);
            if (USER_X == null)
                throw new Exception("USER_X is null.");
            ViewBag.UserDept = USER_X.Department;
            ViewBag.UserCompany = USER_X.Company;

            var sites = user_x.GetSiteBranchByID(USER_X.ID);
            var t = sites.FirstOrDefault(s => s.Name == USER_X.Company);
            if (t != null)
            {
                usersiteid = t.ID;
                if (t.Alias.Equals("省局"))
                {
                    if (USER_X.Department == "中软海晟技术支持组")
                    {
                        usersite = "中软海晟技术支持组";
                    }
                    else
                    {
                        usersite = "福建省烟草专卖局";
                    }
                }
                else
                {
                    usersite = t.Alias;
                }
            }
            if (usersiteid == 101 && USER_X.Department != "省局机关运维" && USER_X.Department != "中软海晟技术支持组")
                return View("Index");
            else if (usersiteid >= 102 || USER_X.Department == "省局机关运维" || USER_X.Department == "中软海晟技术支持组")
                return View("SiteView");
            else
                throw new Exception("无法获取地市信息，用户无权访问该页面。");
        }

        public JsonResult GetURL(string type, string ids)
        {
            string url = string.Empty;
            string requestid = string.Empty;
            string connectionStr = string.Empty;
            string queryStr = string.Empty;
            if (!string.IsNullOrEmpty(type))
            {
                if (type.Equals("自服务"))
                {
                    url = string.Format(ConfigurationManager.AppSettings["SelfRequestUrl"], ids.Substring(4));
                }
                else if (type.Equals("事件"))
                {
                    connectionStr = ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString;
                    queryStr = string.Format("Select Entry_ID as RequestID from HPD_Help_Desk where incident_number='{0}'", ids);
                    requestid = GetRequestID(queryStr, connectionStr);
                    url = string.Format(ConfigurationManager.AppSettings["ITSMRequestUrl"], "HPD:Help Desk", requestid);
                }
                else if (type.Equals("变更"))
                {
                    connectionStr = ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString;
                    queryStr = string.Format("Select Request_ID as RequestID from chg_infrastructure_change where infrastructure_change_id='{0}'", ids);
                    requestid = GetRequestID(queryStr, connectionStr);
                    if (ToDoHelper.IsChangeApprover(requestid, User.Identity.Name))
                    {
                        url = ConfigurationManager.AppSettings["ApprovalCenter"];
                    }
                    else
                    {
                        url = string.Format(ConfigurationManager.AppSettings["ITSMRequestUrl"], "CHG:Infrastructure Change", requestid);
                    }
                }
                else if (type.Equals("问题"))
                {
                    connectionStr = ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString;
                    queryStr = string.Format("Select sys_problem_investigation_id as RequestID from pbm_problem_investigation where Problem_Investigation_ID='{0}'", ids);
                    requestid = GetRequestID(queryStr, connectionStr);
                    url = string.Format(ConfigurationManager.AppSettings["ITSMRequestUrl"], "PBM:Problem Investigation", requestid);
                }
                else if (type.Equals("任务"))
                {
                    url = string.Format(ConfigurationManager.AppSettings["ITSMRequestUrl"], "TMS:Task", ids);
                }
            }
            return Json(Server.UrlEncode(url), JsonRequestBehavior.AllowGet);
        }

        public string GetRequestID(string querystr, string connectionStr)
        {
            string requestid = string.Empty;
            if (!string.IsNullOrEmpty(connectionStr))
            {
                using (OracleConnection con = new OracleConnection(connectionStr))
                {
                    con.Open();
                    OracleCommand command;
                    command = con.CreateCommand();
                    command.CommandType = System.Data.CommandType.Text;
                    command.BindByName = true;
                    command.CommandText = querystr;
                    var reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        requestid = reader["RequestID"].ToString();
                    }
                }
            }
            return requestid;
        }

        public ActionResult GetReport(string startdate, string enddate, string location)
        {
            Reporting_model model = new Reporting_model();
            if (string.IsNullOrEmpty(startdate))
            {
                startdate = string.Format("{0:MM/dd/yyyy HH:mm:ss}", DateTime.MinValue);
            }
            if (string.IsNullOrEmpty(enddate))
            {
                enddate = string.Format("{0:MM/dd/yyyy HH:mm:ss}", DateTime.Now);
            }
            if (!string.IsNullOrEmpty(startdate) && !string.IsNullOrEmpty(enddate))
            {
                //string connectionStr = ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString;

                model = Search(location, startdate, enddate);
            }
            return Json(model);
        }

        public ActionResult GetSiteReport(string startdate, string enddate)
        {
            Reporting_model model = new Reporting_model();
            if (string.IsNullOrEmpty(startdate))
            {
                startdate = string.Format("{0:MM/dd/yyyy HH:mm:ss}", DateTime.MinValue);
            }
            if (string.IsNullOrEmpty(enddate))
            {
                enddate = string.Format("{0:MM/dd/yyyy HH:mm:ss}", DateTime.Now);
            }
            if (!string.IsNullOrEmpty(startdate) && !string.IsNullOrEmpty(enddate))
            {
                model = SiteSearch(usersiteid.ToString(), startdate, enddate);
            }
            return Json(model);
        }

        public Reporting_model SiteSearch(string local, string startdate, string enddate)
        {
            string connectionStr = ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString;
            string connectionStrSF = ConfigurationManager.ConnectionStrings["SelfServiceContext"].ConnectionString;
            Reporting_model model = new Reporting_model();
            string companyname = string.Empty;
            using (OracleConnection con = new OracleConnection(connectionStr))
            {
                con.Open();
                OracleCommand command;
                command = con.CreateCommand();
                command.CommandType = System.Data.CommandType.Text;
                command.BindByName = true;
                command.CommandText = string.Format("select name from sitebranch where id={0}", int.Parse(local));
                var reader = command.ExecuteReader();
                while (reader.Read())
                {
                    companyname = reader["name"].ToString();
                }
            }
            model.categories.Add("服务请求");
            using (OracleConnection con = new OracleConnection(connectionStrSF))
            {
                con.Open();
                OracleCommand command;
                command = con.CreateCommand();
                command.CommandType = System.Data.CommandType.Text;
                command.BindByName = true;
                command.CommandText = string.Format("select count(*) as unresolvecount from servicerecords where companyids = '{0}' and CreateTime>=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and createtime<=to_date('{2}','mm/dd/yyyy hh24:mi:ss') and stauts<4 and stauts>0", companyname, startdate, enddate);
                var reader = command.ExecuteReader();
                while (reader.Read())
                {
                    model.seriesdata.Add(int.Parse(reader["unresolvecount"].ToString()));
                }
                command.CommandText = string.Format("select count(*) as resolvecount from servicerecords where companyids = '{0}' and CreateTime>=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and createtime<=to_date('{2}','mm/dd/yyyy hh24:mi:ss') and stauts>=4", companyname, startdate, enddate);
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    model.seriesdata2.Add(int.Parse(reader["resolvecount"].ToString()));
                }
            }
            model.categories.Add("事件");
            model.categories.Add("变更");
            using (OracleConnection con = new OracleConnection(connectionStr))
            {
                con.Open();
                OracleCommand command;
                command = con.CreateCommand();
                command.CommandType = System.Data.CommandType.Text;
                command.BindByName = true;
                command.CommandText = string.Format("select count(*) as unresolvecount from incident where siteid={0} and TicketCreateTime>=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and ticketcreatetime<=to_date('{2}','mm/dd/yyyy hh24:mi:ss') and status<4 and status>0", int.Parse(local), startdate, enddate);
                var reader = command.ExecuteReader();
                while (reader.Read())
                {
                    model.seriesdata.Add(int.Parse(reader["unresolvecount"].ToString()));
                }
                command.CommandText = string.Format("select count(*) as resolvecount from incident where siteid={0} and TicketCreateTime>=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and ticketcreatetime<=to_date('{2}','mm/dd/yyyy hh24:mi:ss') and status>=4", int.Parse(local), startdate, enddate);
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    model.seriesdata2.Add(int.Parse(reader["resolvecount"].ToString()));
                }
                command.CommandText = string.Format("select count(*) as unresolvecount from change where siteid={0} and TicketCreateTime>=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and ticketcreatetime<=to_date('{2}','mm/dd/yyyy hh24:mi:ss') and status<9", int.Parse(local), startdate, enddate);
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    model.seriesdata.Add(int.Parse(reader["unresolvecount"].ToString()));
                }
                command.CommandText = string.Format("select count(*) as resolvecount from change where siteid={0} and TicketCreateTime>=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and ticketcreatetime<=to_date('{2}','mm/dd/yyyy hh24:mi:ss') and status>=9", int.Parse(local), startdate, enddate);
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    model.seriesdata2.Add(int.Parse(reader["resolvecount"].ToString()));
                }
            }
            return model;
        }

        public Reporting_model Search(string local, string startdate, string enddate)
        {
            string connectionStr = ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString;
            string connectionStrSF = ConfigurationManager.ConnectionStrings["SelfServiceContext"].ConnectionString;

            Reporting_model model = new Reporting_model();
            SiteBranch site = new SiteBranch();
            SiteBranchModel sitebranch = site.GetSiteBranchByID(int.Parse(local));
            var companyname = string.Empty;
            if (sitebranch != null)
            {
                companyname = sitebranch.Name;
            }
            model.categories.Add("自服务");
            using (OracleConnection con = new OracleConnection(connectionStrSF))
            {
                double all = 0.0;
                double solve = 0.0;
                con.Open();
                OracleCommand command;
                command = con.CreateCommand();
                command.CommandType = System.Data.CommandType.Text;
                command.BindByName = true;
                command.CommandText = string.Format("select count(*) as unresolvecount from servicerecords where companyids = '{0}' and CreateTime>=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and createtime<=to_date('{2}','mm/dd/yyyy hh24:mi:ss') and stauts!=0", companyname, startdate, enddate);
                var reader = command.ExecuteReader();
                while (reader.Read())
                {
                    all = int.Parse(reader["unresolvecount"].ToString());
                }
                command.CommandText = string.Format("select count(*) as resolvecount from servicerecords where companyids = '{0}' and CreateTime>=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and createtime<=to_date('{2}','mm/dd/yyyy hh24:mi:ss') and stauts>=4", companyname, startdate, enddate);
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    solve = int.Parse(reader["resolvecount"].ToString());
                }
                model.seriesdata.Add((int)all);
                model.seriesdata2.Add((int)(all - solve));
                if (solve != 0 && all != 0)
                {
                    model.seriesdata3.Add(ToFixed(((solve / all) * 100),2));
                }
                else
                {
                    model.seriesdata3.Add(0.00);
                }
            }
            model.categories.Add("事件");
            model.categories.Add("变更");
            using (OracleConnection con = new OracleConnection(connectionStr))
            {
                double allinc = 0.0;
                double solveinc = 0.0;
                double allchg = 0.0;
                double solvechg = 0.0;
                con.Open();
                OracleCommand command;
                command = con.CreateCommand();
                command.CommandType = System.Data.CommandType.Text;
                command.BindByName = true;
                command.CommandText = string.Format("select count(*) as unresolvecount from incident where siteid={0} and TicketCreateTime>=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and ticketcreatetime<=to_date('{2}','mm/dd/yyyy hh24:mi:ss') and status!=0", int.Parse(local), startdate, enddate);
                var reader = command.ExecuteReader();
                while (reader.Read())
                {
                    allinc = int.Parse(reader["unresolvecount"].ToString());
                }
                command.CommandText = string.Format("select count(*) as resolvecount from incident where siteid={0} and TicketCreateTime>=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and ticketcreatetime<=to_date('{2}','mm/dd/yyyy hh24:mi:ss') and status>=4", int.Parse(local), startdate, enddate);
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    solveinc = int.Parse(reader["resolvecount"].ToString());
                }
                model.seriesdata.Add((int)allinc);
                model.seriesdata2.Add((int)(allinc - solveinc));
                if (solveinc != 0 && allinc != 0)
                {
                    model.seriesdata3.Add(ToFixed(((solveinc / allinc) * 100),2));
                }
                else
                {
                    model.seriesdata3.Add(0.0);
                }
                command.CommandText = string.Format("select count(*) as unresolvecount from change where siteid={0} and TicketCreateTime>=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and ticketcreatetime<=to_date('{2}','mm/dd/yyyy hh24:mi:ss')", int.Parse(local), startdate, enddate);
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    allchg = int.Parse(reader["unresolvecount"].ToString());
                }
                command.CommandText = string.Format("select count(*) as resolvecount from change where siteid={0} and TicketCreateTime>=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and ticketcreatetime<=to_date('{2}','mm/dd/yyyy hh24:mi:ss') and status>=9", int.Parse(local), startdate, enddate);
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    solvechg = int.Parse(reader["resolvecount"].ToString());
                }
                model.seriesdata.Add((int)allchg);
                model.seriesdata2.Add((int)(allchg - solvechg));
                if (solvechg != 0 && allchg != 0)
                {
                    model.seriesdata3.Add(ToFixed(((solvechg / allchg) * 100),2));
                }
                else
                {
                    model.seriesdata3.Add(0.0);
                }
            }
            return model;
        }

        public ActionResult GetSitePieReport(string startdate, string enddate, string type)
        {
            // Reporting_model model = new Reporting_model();
            List<object> model = new List<object>();
            string connectionStr = string.Empty;
            string queryStr1 = string.Empty;
            string queryStr2 = string.Empty;
            if (string.IsNullOrEmpty(startdate))
            {
                startdate = string.Format("{0:MM/dd/yyyy HH:mm:ss}", DateTime.MinValue);
            }
            if (string.IsNullOrEmpty(enddate))
            {
                enddate = string.Format("{0:MM/dd/yyyy HH:mm:ss}", DateTime.Now);
            }
            if (!string.IsNullOrEmpty(startdate) && !string.IsNullOrEmpty(enddate))
            {
                switch (type)
                {
                    case "service":
                        connectionStr = ConfigurationManager.ConnectionStrings["SelfServiceContext"].ConnectionString;
                        queryStr1 = string.Format("select count(*) as unresolvecount from servicerecords where CreateTime>=to_date('{0}','mm/dd/yyyy hh24:mi:ss') and createtime<=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and stauts < 4 and stauts > 0 and companyids like '%{2}%'", startdate, enddate, usersite);
                        queryStr2 = string.Format("select count(*) as resolvecount from servicerecords where CreateTime>=to_date('{0}','mm/dd/yyyy hh24:mi:ss') and createtime<=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and stauts >= 4 and companyids like '%{2}%'", startdate, enddate, usersite);
                        break;
                    case "incident":
                        connectionStr = ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString;
                        queryStr1 = string.Format("select count(*) as unresolvecount from incident where TicketCreateTime>=to_date('{0}','mm/dd/yyyy hh24:mi:ss') and ticketcreatetime<=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and status < 4 and status >0 and siteid={2}", startdate, enddate, usersiteid);
                        queryStr2 = string.Format("select count(*) as resolvecount from incident where TicketCreateTime>=to_date('{0}','mm/dd/yyyy hh24:mi:ss') and ticketcreatetime<=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and status >= 4 and siteid={2}", startdate, enddate, usersiteid);
                        break;
                    case "change":
                        connectionStr = ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString;
                        queryStr1 = string.Format("select count(*) as unresolvecount from change where TicketCreateTime>=to_date('{0}','mm/dd/yyyy hh24:mi:ss') and ticketcreatetime<=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and status < 9 and siteid={2}", startdate, enddate, usersiteid);
                        queryStr2 = string.Format("select count(*) as resolvecount from change where TicketCreateTime>=to_date('{0}','mm/dd/yyyy hh24:mi:ss') and ticketcreatetime<=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and status >= 9 and siteid={2}", startdate, enddate, usersiteid);
                        break;
                }
                model = SearchPie(connectionStr, queryStr1, queryStr2);
            }
            return Json(model);
        }

        public List<object> SearchPie(string connectionStr, string queryStr1, string queryStr2)
        {
            // Reporting_model model = new Reporting_model();
            List<object> modelList = new List<object>();
            using (OracleConnection con = new OracleConnection(connectionStr))
            {
                con.Open();
                OracleCommand command;
                command = con.CreateCommand();
                command.CommandType = System.Data.CommandType.Text;
                command.BindByName = true;
                command.CommandText = queryStr1;
                var reader = command.ExecuteReader();
                while (reader.Read())
                {
                    List<object> model = new List<object>();
                    model.Add("未解决");
                    model.Add(int.Parse(reader["unresolvecount"].ToString()));
                    modelList.Add(model);
                }
                command.CommandText = queryStr2;
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    List<object> model = new List<object>();
                    model.Add("已解决");
                    model.Add(int.Parse(reader["resolvecount"].ToString()));
                    modelList.Add(model);
                }
                return modelList;
            }
        }

        public ActionResult GetCount(string startdate, string enddate)
        {
            IDictionary<string, string> counts = new Dictionary<string, string>();
            if (string.IsNullOrEmpty(startdate))
            {
                startdate = string.Format("{0:MM/dd/yyyy HH:mm:ss}", DateTime.MinValue);
            }
            if (string.IsNullOrEmpty(enddate))
            {
                enddate = string.Format("{0:MM/dd/yyyy HH:mm:ss}", DateTime.Now);
            }
            if (!string.IsNullOrEmpty(startdate) && !string.IsNullOrEmpty(enddate))
            {
                string connectionStrS = ConfigurationManager.ConnectionStrings["SelfServiceContext"].ConnectionString;
                string connectionStrU = ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString;

                using (OracleConnection con = new OracleConnection(connectionStrS))
                {
                    con.Open();
                    OracleCommand command;
                    command = con.CreateCommand();
                    command.CommandType = System.Data.CommandType.Text;
                    command.BindByName = true;
                    command.CommandText = string.Format("select count(*) as servicecount from servicerecords where CreateTime>=to_date('{0}','mm/dd/yyyy hh24:mi:ss') and createtime<=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and stauts!=0", startdate, enddate);
                    var reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        counts.Add("servicecount", reader["servicecount"].ToString());
                    }
                    command.CommandText = string.Format("select count(*) as serviceunresolvecount from servicerecords where CreateTime>=to_date('{0}','mm/dd/yyyy hh24:mi:ss') and createtime<=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and stauts<4 and stauts>0", startdate, enddate);
                    reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        counts.Add("serviceunresolvecount", reader["serviceunresolvecount"].ToString());
                    }
                }
                using (OracleConnection con = new OracleConnection(connectionStrU))
                {
                    con.Open();
                    OracleCommand command;
                    command = con.CreateCommand();
                    command.CommandType = System.Data.CommandType.Text;
                    command.BindByName = true;
                    command.CommandText = string.Format("select count(*) as incidentcount from incident where TicketCreateTime>=to_date('{0}','mm/dd/yyyy hh24:mi:ss') and ticketcreatetime<=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and status!=0", startdate, enddate);
                    var reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        counts.Add("incidentcount", reader["incidentcount"].ToString());
                    }
                    command.CommandText = string.Format("select count(*) as incidentunsolvecount from incident where TicketCreateTime>=to_date('{0}','mm/dd/yyyy hh24:mi:ss') and ticketcreatetime<=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and status<4 and status>0", startdate, enddate);
                    reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        counts.Add("incidentunsolvecount", reader["incidentunsolvecount"].ToString());
                    }
                    command.CommandText = string.Format("select count(*) as changecount from change where TicketCreateTime>=to_date('{0}','mm/dd/yyyy hh24:mi:ss') and ticketcreatetime<=to_date('{1}','mm/dd/yyyy hh24:mi:ss')", startdate, enddate);
                    reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        counts.Add("changecount", reader["changecount"].ToString());
                    }
                    command.CommandText = string.Format("select count(*) as changunsolvecount from change where TicketCreateTime>=to_date('{0}','mm/dd/yyyy hh24:mi:ss') and ticketcreatetime<=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and status<9", startdate, enddate);
                    reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        counts.Add("changunsolvecount", reader["changunsolvecount"].ToString());
                    }
                }
            }
            return Json(counts);
        }
        //public string GetCompanybyID(string local)
        //{
        //    string connectionStr = ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString;
        //    string companyname = string.Empty;
        //    using (OracleConnection con = new OracleConnection(connectionStr))
        //    {
        //        con.Open();
        //        OracleCommand command;
        //        command = con.CreateCommand();
        //        command.CommandType = System.Data.CommandType.Text;
        //        command.BindByName = true;
        //        command.CommandText = string.Format("select name from sitebranch where id={0}", int.Parse(local));
        //        var reader = command.ExecuteReader();
        //        while (reader.Read())
        //        {
        //            companyname = reader["name"].ToString();
        //        }
        //    }
        //    return companyname;
        //}

        public ActionResult GetTableData(string type, string startdate, string enddate, string local)
        {

            TableModel tablemodel = new TableModel();
            if (string.IsNullOrEmpty(startdate))
            {
                startdate = string.Format("{0:MM/dd/yyyy HH:mm:ss}", DateTime.MinValue);
            }
            if (string.IsNullOrEmpty(enddate))
            {
                enddate = string.Format("{0:MM/dd/yyyy HH:mm:ss}", DateTime.Now);
            }
            if (!string.IsNullOrEmpty(startdate) && !string.IsNullOrEmpty(enddate))
            {
                int pageIndex, pageSize;
                string connectionStr = string.Empty;
                StringBuilder queryStr = new StringBuilder();
                if (int.TryParse(Request.Form["page"], out pageIndex) == false || int.TryParse(Request.Form["rows"], out pageSize) == false)
                {
                    throw new Exception("获取页面信息失败");
                }
                var sortitem = Request.Form["sort"];
                var sortorder = Request.Form["order"];
                if (string.IsNullOrEmpty(sortitem) || string.IsNullOrEmpty(sortorder))
                {
                    sortitem = "CreateTime";
                    sortorder = "desc";
                }
                switch (type)
                {
                    case "service":
                        connectionStr = ConfigurationManager.ConnectionStrings["SelfServiceContext"].ConnectionString;
                        queryStr = queryStr.Append(string.Format("select 'SFR0'||servicerequestid as IDs,'自服务' as TypeS, description as Description, bdsummary as BDSummary, CASE stauts WHEN 0 THEN '草稿'  WHEN 1 THEN '审批中' WHEN 2 THEN '等待中' WHEN 3 THEN '处理中' ELSE '其他' END as Status,CASE  s.step WHEN 1 THEN '审批人' WHEN 2 THEN '主任' WHEN 3 THEN '审批人'  WHEN 4 THEN '主任'  ELSE '' END as AssigneeGroup,''as Assignee, createtime as CreateTime,a.username as Submitter from servicerecords s left join accountcopy a on a.accountid=s.accountid where s.CreateTime>=to_date('{0}','mm/dd/yyyy hh24:mi:ss') and s.createtime<=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and s.stauts < 4 and s.stauts > 0 ", startdate, enddate));
                        if (!string.IsNullOrEmpty(local))
                        {
                            SiteBranch site = new SiteBranch();
                            SiteBranchModel sitebranch = site.GetSiteBranchByID(int.Parse(local));
                            var companyname = string.Empty;
                            if (sitebranch != null)
                            {
                                companyname = sitebranch.Name;
                            }
                            queryStr = queryStr.Append(string.Format("and  companyids = '{0}' ", companyname));
                        }
                        queryStr = queryStr.Append(string.Format("order by {0} {1}", sortitem, sortorder));
                        break;
                    case "incident":
                        connectionStr = ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString;
                        queryStr = queryStr.Append(string.Format("select request_id as IDs, '事件' as TypeS, summury as Description, '' as BDSummary,  CASE status  WHEN 0 THEN '新建'  WHEN 1 THEN '已指派' WHEN 2 THEN '处理中' WHEN 3 THEN '暂停' WHEN 4 THEN '已解决' WHEN 5 THEN '已关闭' WHEN 6 THEN '已取消' ELSE '其他' END as Status,assigned_group as AssigneeGroup,assignee as Assignee, ticketcreatetime as CreateTime, submitter_full_name as Submitter from incident where TicketCreateTime>=to_date('{0}','mm/dd/yyyy hh24:mi:ss') and ticketcreatetime<=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and status<4 and status>0 ", startdate, enddate));
                        if (!string.IsNullOrEmpty(local))
                        {
                            queryStr = queryStr.Append(string.Format("and siteid={0} ", int.Parse(local)));
                        }
                        queryStr = queryStr.Append(string.Format("order by {0} {1}", sortitem, sortorder));
                        break;
                    case "change":
                        connectionStr = ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString;
                        queryStr = queryStr.Append(string.Format("select request_id as IDs, '变更' as TypeS, Description as Description, '' as BDSummary, CASE status  WHEN 0 THEN '已提交'  WHEN 1 THEN '已设计'  WHEN 2 THEN '变更请求' WHEN 3 THEN '进行中的计划' WHEN 7 THEN '正在执行' WHEN 8 THEN '暂停' ELSE '其他' END as Status, ASGRP as AssigneeGroup,ASCHG as Assignee, ticketcreatetime as CreateTime,submitter_full_name as Submitter from change where TicketCreateTime>=to_date('{0}','mm/dd/yyyy hh24:mi:ss') and ticketcreatetime<=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and status<9 ", startdate, enddate));
                         if (!string.IsNullOrEmpty(local))
                        {
                            queryStr = queryStr.Append(string.Format("and siteid={0} ", int.Parse(local)));
                        }
                        queryStr = queryStr.Append(string.Format("order by {0} {1}", sortitem, sortorder));
                        break;
                }


                List<ApplicationModel> appModelList = new List<ApplicationModel>();
                List<ApplicationModel> appModelList2 = new List<ApplicationModel>();
                if (!string.IsNullOrEmpty(connectionStr))
                {
                    using (OracleConnection con = new OracleConnection(connectionStr))
                    {
                        con.Open();
                        OracleCommand command;
                        command = con.CreateCommand();
                        command.CommandType = System.Data.CommandType.Text;
                        command.BindByName = true;
                        command.CommandText = queryStr.ToString();
                        var reader = command.ExecuteReader();
                        while (reader.Read())
                        {
                            ApplicationModel item = new ApplicationModel();

                            item.IDS = reader["IDS"].ToString();
                            item.TypeS = (string)reader["TypeS"];
                            item.Description = reader["Description"] == DBNull.Value ? reader["BDSummary"].ToString() : reader["Description"].ToString();
                            item.Status = reader["Status"].ToString();
                            item.CreateTime = reader["CreateTime"] == DBNull.Value ? "" : DateTime.Parse(reader["CreateTime"].ToString()).ToString("yyyy/MM/dd HH:mm:ss");
                            item.AssigneeGroup = reader["AssigneeGroup"].ToString();
                            item.Assignee = reader["Assignee"].ToString();
                            item.Submitter = reader["Submitter"].ToString();

                            appModelList.Add(item);
                        }
                    }
                }
                
                tablemodel.total = appModelList.Count();
                if (pageIndex > 0 && tablemodel.total > (pageSize * (pageIndex - 1)))
                {
                    appModelList2 = appModelList.Skip(pageSize * (pageIndex - 1)).Take(pageSize).ToList();
                }
                else
                {
                    appModelList2 = appModelList.ToList();
                }
                tablemodel.rows = appModelList2;
            }
            return Json(tablemodel);
        }

        public ActionResult GetSiteTableData(string startdate, string enddate)
        {
            TableModel tablemodel = new TableModel();
            if (string.IsNullOrEmpty(startdate))
            {
                startdate = string.Format("{0:MM/dd/yyyy HH:mm:ss}", DateTime.MinValue);
            }
            if (string.IsNullOrEmpty(enddate))
            {
                enddate = string.Format("{0:MM/dd/yyyy HH:mm:ss}", DateTime.Now);
            }
            if (!string.IsNullOrEmpty(startdate) && !string.IsNullOrEmpty(enddate))
            {
                int pageIndex, pageSize;
                if (int.TryParse(Request.Form["page"], out pageIndex) == false || int.TryParse(Request.Form["rows"], out pageSize) == false)
                {
                    throw new Exception("获取页面信息失败");
                }

                var sortitem = Request.Form["sort"];
                var sortorder = Request.Form["order"];
                if (string.IsNullOrEmpty(sortitem) || string.IsNullOrEmpty(sortorder))
                {
                    sortitem = "CreateTime";
                    sortorder = "desc";
                }
                string connectionStrSF = ConfigurationManager.ConnectionStrings["SelfServiceContext"].ConnectionString;
                string queryStrSF = string.Format("select 'SFR0'||s.servicerequestid as IDs,'自服务' as TypeS, s.description as Description, s.bdsummary as BDSummary, CASE s.stauts WHEN 0 THEN '草稿'  WHEN 1 THEN '审批中' WHEN 2 THEN '等待中' WHEN 3 THEN '处理中' ELSE '其他' END as Status, CASE s.step WHEN 1 THEN '审批人' WHEN 2 THEN '主任' WHEN 3 THEN '审批人'  WHEN 4 THEN '主任'  ELSE '' END as AssigneeGroup,'' as Assignee, s.createtime as CreateTime,a.username as Submitter  from servicerecords s left join accountcopy a on a.accountid=s.accountid where CreateTime>=to_date('{0}','mm/dd/yyyy hh24:mi:ss') and CreateTime<=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and s.stauts < 4 and s.stauts > 0 and s.companyids like '%{2}%'", startdate, enddate, usersite);

                string connectionStr = ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString;
                string queryStrIncident = string.Format("select request_id as IDs,'事件' as TypeS, summury as Description, CASE status  WHEN 0 THEN '新建'  WHEN 1 THEN '已指派' WHEN 2 THEN '处理中' WHEN 3 THEN '暂停' WHEN 4 THEN '已解决' WHEN 5 THEN '已关闭' WHEN 6 THEN '已取消' ELSE '其他' END as Status, assigned_group as AssigneeGroup,assignee as Assignee, ticketcreatetime as CreateTime, submitter_full_name as Submitter from incident where TicketCreateTime>=to_date('{0}','mm/dd/yyyy hh24:mi:ss') and ticketcreatetime<=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and status<4 and status>0  and siteid = {2}", startdate, enddate, usersiteid);

                string queryStrChange = string.Format("select request_id as IDs,'变更' as TypeS, Description as Description, CASE status  WHEN 0 THEN '已提交'  WHEN 1 THEN '已设计'  WHEN 2 THEN '变更请求' WHEN 3 THEN '进行中的计划' WHEN 7 THEN '正在执行' WHEN 8 THEN '暂停' ELSE '其他' END as Status,ASGRP as AssigneeGroup,ASCHG as Assignee, ticketcreatetime as CreateTime, submitter_full_name as Submitter from change where TicketCreateTime>=to_date('{0}','mm/dd/yyyy hh24:mi:ss') and ticketcreatetime<=to_date('{1}','mm/dd/yyyy hh24:mi:ss') and status < 9 and siteid = {2}", startdate, enddate, usersiteid);


                List<ApplicationModel> appModelList = new List<ApplicationModel>();
                List<ApplicationModel> appModelList1 = new List<ApplicationModel>();
                List<ApplicationModel> appModelList2 = new List<ApplicationModel>();
                List<ApplicationModel> appModelList3 = new List<ApplicationModel>();
               // List<ApplicationModel> appModelList4 = new List<ApplicationModel>();

                if (!string.IsNullOrEmpty(connectionStrSF))
                {
                    using (OracleConnection con = new OracleConnection(connectionStrSF))
                    {
                        con.Open();
                        OracleCommand command;
                        command = con.CreateCommand();
                        command.CommandType = System.Data.CommandType.Text;
                        command.BindByName = true;
                        command.CommandText = queryStrSF;
                        var reader = command.ExecuteReader();
                        while (reader.Read())
                        {
                            ApplicationModel item = new ApplicationModel();

                            item.IDS = reader["IDS"].ToString();
                            item.TypeS = (string)reader["TypeS"];
                            item.Description = reader["Description"] == DBNull.Value ? reader["BDSummary"].ToString() : reader["Description"].ToString();
                            item.Status = reader["Status"].ToString();
                            item.AssigneeGroup = reader["AssigneeGroup"].ToString();
                            item.Assignee = reader["Assignee"].ToString();
                            item.CreateTime = reader["CreateTime"] == DBNull.Value ? "" : DateTime.Parse(reader["CreateTime"].ToString()).ToString("yyyy/MM/dd HH:mm:ss");
                            item.Submitter = reader["Submitter"].ToString();

                            appModelList1.Add(item);
                        }
                        appModelList.AddRange(appModelList1);
                    }
                }
                if (!string.IsNullOrEmpty(connectionStr))
                {
                    using (OracleConnection con = new OracleConnection(connectionStr))
                    {
                        con.Open();
                        OracleCommand command;
                        command = con.CreateCommand();
                        command.CommandType = System.Data.CommandType.Text;
                        command.BindByName = true;
                        command.CommandText = queryStrIncident;
                        var reader = command.ExecuteReader();
                        while (reader.Read())
                        {
                            ApplicationModel item = new ApplicationModel();

                            item.IDS = reader["IDS"].ToString();
                            item.TypeS = (string)reader["TypeS"];
                            item.Description = reader["Description"].ToString();
                            item.Status = reader["Status"].ToString();
                            item.AssigneeGroup = reader["AssigneeGroup"].ToString();
                            item.Assignee = reader["Assignee"].ToString();
                            item.CreateTime = reader["CreateTime"] == DBNull.Value ? "" : DateTime.Parse(reader["CreateTime"].ToString()).ToString("yyyy/MM/dd HH:mm:ss");
                            item.Submitter = reader["Submitter"].ToString();

                            appModelList2.Add(item);
                        }
                        appModelList.AddRange(appModelList2);
                        command.CommandText = queryStrChange;
                        reader = command.ExecuteReader();
                        while (reader.Read())
                        {
                            ApplicationModel item = new ApplicationModel();

                            item.IDS = reader["IDS"].ToString();
                            item.TypeS = (string)reader["TypeS"];
                            item.Description = reader["Description"].ToString();
                            item.Status = reader["Status"].ToString();
                            item.AssigneeGroup = reader["AssigneeGroup"].ToString();
                            item.Assignee = reader["Assignee"].ToString();
                            item.CreateTime = reader["CreateTime"] == DBNull.Value ? "" : DateTime.Parse(reader["CreateTime"].ToString()).ToString("yyyy/MM/dd HH:mm:ss");
                            item.Submitter = reader["Submitter"].ToString();

                            appModelList3.Add(item);
                        }
                        appModelList.AddRange(appModelList3);
                    }
                }
                //appModelList4 = appModelList.OrderByDescending(o=>o.CreateTime).ToList();
                var c = ((Expression<Func<ApplicationModel, string>>)ToDoHelper.GenerateSelector<ApplicationModel>(sortitem)).Compile();
                if (sortorder == "asc")
                {
                    appModelList = appModelList.OrderBy(c).ToList();
                }
                else
                {
                    appModelList = appModelList.OrderByDescending(c).ToList();
                }
                tablemodel.total = appModelList.Count();

                if (pageIndex > 0 && tablemodel.total > (pageSize * (pageIndex - 1)))
                {
                    appModelList = appModelList.Skip(pageSize * (pageIndex - 1)).Take(pageSize).ToList();
                }
                else
                {
                    appModelList = appModelList.ToList();
                }

                tablemodel.rows = appModelList;
            }
            return Json(tablemodel);
        }

        #region    //截取小数，且不进行四舍五入操作

        public static double ToFixed(double d, int s)
        {
            double sp = Math.Pow(10, s);
            if (d < 0)
                return Math.Truncate(d) + Math.Ceiling((d - Math.Truncate(d)) * sp) / sp;
            else
                return Math.Truncate(d) + Math.Floor((d - Math.Truncate(d)) * sp) / sp;
        }

        #endregion
    }

    public class Reporting_model
    {
        public List<string> categories { get; set; }

        public List<int> seriesdata { get; set; }

        public List<int> seriesdata2 { get; set; }

        public List<double> seriesdata3 { get; set; }

        public Reporting_model()
        {
            categories = new List<string>();
            seriesdata3 = new List<double>();
            seriesdata = new List<int>();
            seriesdata2 = new List<int>();
        }
    }

    public class TableModel
    {
        public int total;
        public List<ApplicationModel> rows;
    }
}
